/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.compomics.seedlistgenerator;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Level;
import org.apache.log4j.Logger;

/**
 *
 * @author Jonas
 */
public class DataProvider {

    /* 
     * Define the query String which will be passed to ms_lims
     * In the last line you find "substring(spectrum.filename,2,4)"
     * The numbers 2 and 4 need to be edited according to the style of the runnumbers used earlier 
     */
    private static final String QUERY = "select identification.precursor as precursorMZ, scan.rtsec as rtSec, spectrum.total_spectrum_intensity as totSpecInt"
            + " from identification, scan, spectrum"
            + " where l_projectid = ? and spectrum.spectrumid = scan.l_spectrumid and identification.l_spectrumid = spectrum.spectrumid"
            + " and substring(spectrum.filename,2,4) = ? order by rtSec";
    
    private static final Logger LOGGER = Logger.getLogger(DataProvider.class);
    
    // Define the credentials used to establish the connection with the database
    JDBCConnection jDBCConnection = new JDBCConnection("enter_username", "enter_user_password", "jdbc:mysql://muppet03.ugent.be/projects");

    /*
     * This method executes the query in ms_lims and stores every returned entry in identificationsList
     * identificationsList holds objects of type MS2Identifications
     */
    public List<MS2Identifications> querySpectrumVariables(int projectID, String runNumber) {
        List<MS2Identifications> identificationsList = new ArrayList<MS2Identifications>();
        try {
            Connection connection = jDBCConnection.getDatabaseConnection();
            PreparedStatement stmt = connection.prepareStatement(QUERY);
            stmt.setInt(1, projectID);
            stmt.setString(2, runNumber);

            ResultSet rs = stmt.executeQuery();
            LOGGER.setLevel(Level.ALL);
            LOGGER.info("Starting query...");
            while (rs.next()) {
                double prec = rs.getDouble("precursorMZ");
                double rtSec = rs.getDouble("rtSec");
               
                MS2Identifications identification = new MS2Identifications(prec, rtSec);

                identificationsList.add(identification);
            }

            stmt.close();
            connection.close();
        } catch (SQLException e) {
            LOGGER.error(e.getMessage());
        }
        LOGGER.info("Query finished!");
        return identificationsList;

    }
}
